
/* 

Paper: Gentrification and pioneer businesses 
Authors: Behrens, Boulam, Martin, Mayneris 

Name dofile: table3.do  
Version: 10 nov. 2021 

Output: table 7 on Pioneers vs other creative sectors and non-creative sectors with similar workers  

Inputs:

 - pioneers_final_negbin.dta [produced in .do] [publicly available]
 - list_oth_crea_similar [built by the authors] [publicly available]
 - empl_naics2002_6digit_2006.txt [publicly available (BLS)]
 
*/ 

// deal with employment data 
import delimited empl_naics2002_6digit_2006.txt, clear 
keep if length(naics)==4  
keep naics naicsdscr 
rename naics naics4 
rename naicsdscr naicsdscr4
duplicates drop naics4, force
save tmp, replace 

import delimited empl_naics2002_6digit_2006.txt, clear 
keep if length(naics)==6  
keep naics naicsdscr firm estb empl
collapse (sum) firm estb empl, by(naics naicsdscr)
g naics4=substr(naics,1,4) 
merge m:1 naics4 using tmp
drop _m 
save tmp, replace 

// merge pionners 
use pioneers_final_negbin.dta, clear 
keep naics pioneer_high_negbin
merge m:1 naics using tmp 
replace pioneer=0 if pioneer==.
egen yes=max(pioneer), by(naics4)

foreach i in firm estb empl{
egen tot`i'=sum(`i'), by(naics4) 
g sh`i'=pioneer*`i'/tot`i'
egen shpion_`i'=sum(sh`i'), by(naics4)
}
drop _m
destring naics, replace

// merge with other creative sectors 
merge 1:1 naics using  list_oth_crea_similar
g size=empl/estb
sum size estb if pioneer_high==1, d
sum size estb if crea==1, d
sum size estb if similar==1, d
g lsize=log(size)
g lestb=log(estb)

replace similar=0 if pioneer==1 | crea==1
replace crea=0 if pioneer==1

reg lsize pioneer_high if share_close!=.
outreg2 using "table7.xls",  replace
	foreach i in lsize lestb lrealwage powercouple college share_close share_home mean_age marital children{
	qui reg `i' pioneer_high if share_close!=.
	outreg2 using "table7.xls",  append
	qui reg `i' pioneer_high if (pioneer_high==1 | (similar==0 & crea==1)) & share_close!=.
	outreg2 using "table7.xls",  append 
	qui reg `i' pioneer_high if (pioneer_high==1 | (similar==1 & crea==0)) & share_close!=. 
	outreg2 using "table7.xls",  append
		}

		erase tmp.dta
		
		